﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using Negocio;



namespace Persistencia
{
    class BrokerGroup:Broker
    {
        public BrokerGroup() {
            this.conn = ConnectionDB.getInstance();
        }
        
        public override void add(Object o)
        {
            Group groupAux = (Group)o;
            string sql = "insert into Group_ (oid,name) values(" + groupAux.OID + ",'" + groupAux.Name + "')";
            string sql1 = "update OID set OId = " + groupAux.OID;
            this.conn.connect();
            this.conn.execute(sql);
            this.conn.execute(sql1);
            ObservableCollection<Player> list = groupAux.Players;
            IEnumerator<Player> p = list.GetEnumerator();
            while (p.MoveNext())
            {
                string sql3 = "insert into PlayerGroup (oidGroup, oidPlayer) values(" + groupAux.OID + "," + p.Current.OID + ")";
                this.conn.execute(sql3);
            }
            this.conn.disconnect();
        }
        
        public override void update(Object o)
        {
            Group groupAux = (Group)o;
            string sql = "update Group_ set name= '" + groupAux.Name +"' where oid =" + groupAux.OID;
            string sql1 = "delete from PlayerGroup  where oidGroup =" + groupAux.OID;
            this.conn.connect();
            this.conn.execute(sql);
            this.conn.execute(sql1);
            ObservableCollection<Player> list = groupAux.Players;
            IEnumerator<Player> p = list.GetEnumerator();
            while (p.MoveNext())
            {
                string sql2 = "insert into PlayerGroup (oidGroup, oidPlayer) values(" + groupAux.OID + "," + p.Current.OID + ")";
                this.conn.execute(sql2);
            }            
            this.conn.disconnect();
        }
        
        public override void delete(Object o)
        {
           Group groupAux = (Group)o;
            string sql1 = "delete from PlayerGroup where oidGroup=" + groupAux.OID;
            string sql = "delete from Group_ where oid=" + groupAux.OID;
            this.conn.connect();
            this.conn.execute(sql1);
            this.conn.execute(sql);
            this.conn.disconnect();
        }
        
        public override void bringData(Object o)
        {
            Group groupAux = (Group)o;
            this.conn.connect();
            string sql = "Select * from Group_ where oid =" + groupAux.OID;
            this.conn.bringData(sql);
            while (this.conn.nextRecord())
            {
                groupAux.Name = this.conn.getDataString("name").ToString();
                groupAux.OID = int.Parse(this.conn.getDataString("oid").ToString());
                string sql1 = "Select p.* from PlayerGroup pg, Player p where oid=" + groupAux.OID + " and " + "pg.oidPlayer=p.oid";
                this.conn.bringList(sql1);
                while (this.conn.nextRecord())
                {
                    Player player = new Player();
                    player.Name = this.conn.getDataString("name").ToString();
                    player.IdentityDocument = this.conn.getDataString("identityDocument").ToString();
                    player.FirstLastname = this.conn.getDataString("firstLastname").ToString();
                    player.SecondLastname = this.conn.getDataString("secondLastname").ToString();
                    player.Phone = this.conn.getDataString("phone").ToString();
                    player.BirthDate = (DateTime)this.conn.getDataString("birthDate");
                    player.AdmissionDate = (DateTime)this.conn.getDataString("admissionDate");
                    player.Weight = float.Parse(this.conn.getDataString("weight").ToString());
                    player.EMail = this.conn.getDataString("email").ToString();
                    player.Club = this.conn.getDataString("club").ToString();
                    player.Photo = this.conn.getDataString("photo_").ToString();
                    player.Leg = this.conn.getDataString("leg").ToString();
                    player.Position = this.conn.getDataString("posicion").ToString();
                    player.Enabled = (bool)this.conn.getDataString("enabled");
                    player.OID = int.Parse(this.conn.getDataString("Oid").ToString());
                    groupAux.addPlayer(player);
                }

                }
            this.conn.disconnect();
            
        }

        public override ObservableCollection<Object> bringList(Object o)
        {
            string sql = "Select * from Group_ order by name";
            ObservableCollection<Object> lista = new ObservableCollection<Object>();
            this.conn.connect();
            this.conn.bringList(sql);
            while (this.conn.nextRecord())
            {
                Group group = new Group();
                group.Name = this.conn.getDataString("name").ToString();
                group.OID = int.Parse(this.conn.getDataString("oid").ToString());
               
                lista.Add(group);
            }

            IEnumerator<Object> p = lista.GetEnumerator();
            while (p.MoveNext()) 
            {                
                string sql1 = "Select p.* from PlayerGroup pg, Player p where pg.oidGroup=" + ((Group)p.Current).OID + " and " + "pg.oidPlayer = p.oid order by p.firstLastName, p.secondLastName, p.name";
                this.conn.bringList(sql1);
                while (this.conn.nextRecord())
                {
                    Player player = new Player();
                    player.Name = this.conn.getDataString("name").ToString();
                    player.IdentityDocument = this.conn.getDataString("identityDocument").ToString();
                    player.FirstLastname = this.conn.getDataString("firstLastname").ToString();
                    player.SecondLastname = this.conn.getDataString("secondLastname").ToString();
                    player.Phone = this.conn.getDataString("phone").ToString();
                    player.BirthDate = (DateTime)this.conn.getDataString("birthDate");
                    player.AdmissionDate = (DateTime)this.conn.getDataString("admissionDate");
                    player.Weight = float.Parse(this.conn.getDataString("weight").ToString());
                    player.EMail = this.conn.getDataString("email").ToString();
                    player.Club = this.conn.getDataString("club").ToString();
                    player.Photo = this.conn.getDataString("photo_").ToString();
                    player.Leg = this.conn.getDataString("leg").ToString();
                    player.Position = this.conn.getDataString("posicion").ToString();
                    player.Enabled = (bool)this.conn.getDataString("enabled");
                    player.OID = int.Parse(this.conn.getDataString("Oid").ToString());
                    ((Group)p.Current).addPlayer(player);
                }            
            }
            return lista;
        }
        
    }
}
